
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EBAS_SP_HENT_EN_STUDERENDE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EBAS_SP_HENT_EN_STUDERENDE]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [DBO].[EBAS_SP_HENT_EN_STUDERENDE] 
	@session varchar(255),
	@guid varchar(255)
WITH ENCRYPTION	
AS
	DECLARE 
		@rettighed int,
		@studguid uniqueidentifier,
		@brugerguid uniqueidentifier

	SET NOCOUNT ON
				
	SET @studguid = ( 	SELECT GUID 
				FROM STUDERENDE 
				WHERE SIKKERHED_BRUGER_GUID = (	SELECT SIKKERHED_BRUGER_GUID 
								FROM EBAS_SIKKERHED_SESSION
								WHERE GUID = @session ))

	CREATE TABLE #temp ( rett int )
	INSERT INTO #temp (rett)
	EXEC EBAS.DBO.EBAS_SP_SIKKERHED_VERIFICER_SESSION @session	

	SET @rettighed = (SELECT IsNull(max(rett),0) FROM #temp)

	IF (@guid = @studguid AND 1 <= @rettighed) OR 4 <= @rettighed
	BEGIN
		SET @brugerguid = (	SELECT SIKKERHED_BRUGER_GUID 
					FROM EBAS_STUDERENDE
					WHERE GUID = @guid )
	
		IF @brugerguid IS NULL
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, '' AS BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN
			WHERE	S.IDENTIFIKATION_GUID = IDEN.GUID
			AND	S.STATUS_GUID = ST.GUID
			AND	S.GUID = @guid			
		ELSE
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, B.BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN, 
				EBAS_SIKKERHED_BRUGER B
			WHERE	S.IDENTIFIKATION_GUID = IDEN.GUID
			AND	S.STATUS_GUID = ST.GUID
			AND	S.SIKKERHED_BRUGER_GUID = B.GUID
			AND	S.GUID = @guid		
	END
	ELSE
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, '' AS BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN
			WHERE	S.IDENTIFIKATION_GUID = IDEN.GUID
			AND	S.STATUS_GUID = ST.GUID
			AND	S.GUID = NULL
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
